    /*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.DAO;

import com.aptech.model.PostHistory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Hoang Quy Hong An
 */
public class postHistoryDAO {

    private  Connection myCon;
    private  PreparedStatement ps;
    private  ResultSet rs;
    private  String strCommand = "";

    public  ArrayList<PostHistory> getPostHistoryByUserID(int userID) {
        ArrayList<PostHistory> listPostHistory = new ArrayList<PostHistory>();
        PostHistory psh = null;
        strCommand = "SELECT tbl_PostHistory.* FROM tbl_PostHistory INNER JOIN tbl_RealEstate "
                + "ON tbl_PostHistory.RealEstateID = tbl_RealEstate.RealEstateID "
                + "WHERE tbl_PostHistory.userID = ? AND tbl_RealEstate.realEstateStatus = 1 ";
        try {
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, userID);
            rs = ps.executeQuery();
            while (rs.next()) {
                psh = new PostHistory();
                psh.setPostHistoryID(rs.getInt("postHistoryID"));
                psh.setUserID(rs.getInt("userID"));
                psh.setRealEstateID(rs.getInt("RealEstateID"));
                psh.setStartDate(rs.getString("startDate"));
                psh.setEndDate(rs.getString("endDate"));
                psh.setPostHistoryStatus(rs.getInt("postHistoryStatus"));
                listPostHistory.add(psh);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
        return listPostHistory;
    }

    public boolean insertPosHistory(int userID, int postTypeID, int RealEstateID, String startDate, String endDate, String price) {
        boolean flag = false;

        strCommand = "INSERT INTO tbl_PostHistory (userID ,postTypeID ,RealEstateID ,startDate ,price ,endDate ,postHistoryStatus) "
                + "VALUES (? , ?,? ,? ,? , ?, 1)";
        try {
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, userID);
            ps.setInt(2, postTypeID);
            ps.setInt(3, RealEstateID);
            ps.setString(4, startDate);
            ps.setString(5, price);
            ps.setString(6, endDate);
            int i = ps.executeUpdate();
            if (i != 0) {
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return flag;
    }

    public void closePost(int postHistoryID){
        strCommand = "UPDATE tbl_PostHistory SET postHistoryStatus = 0 WHERE postHistoryID = ?";
        try {
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, postHistoryID);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
    }

    //SELECT userID FROM tbl_PostHistory WHERE RealEstateID = ?/

    public int getUserIDByPost(int RealEstateID) {
        int userID = 0;
        strCommand = "SELECT userID FROM tbl_PostHistory WHERE RealEstateID = ?";
        try {
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, RealEstateID);
            rs = ps.executeQuery();
            while (rs.next()) {
                userID = rs.getInt("userID");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, rs);
        }
        return userID;
    }
}
